DROP VIEW OpenSEEScalarStatView
GO

DROP TABLE EventStat
GO

CREATE TABLE EventStat
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    EventID INT NOT NULL REFERENCES Event(ID),
    VPeak FLOAT NULL,
    VAMax FLOAT NULL,
    VBMax FLOAT NULL,
    VCMax FLOAT NULL,
    VABMax FLOAT NULL,
    VBCMax FLOAT NULL,
    VCAMax FLOAT NULL,
    VAMin FLOAT NULL,
    VBMin FLOAT NULL,
    VCMin FLOAT NULL,
    VABMin FLOAT NULL,
    VBCMin FLOAT NULL,
    VCAMin FLOAT NULL,
    IPeak FLOAT NULL,
    IAMax FLOAT NULL,
    IBMax FLOAT NULL,
    ICMax FLOAT NULL,
    IA2t FLOAT NULL,
    IB2t FLOAT NULL,
    IC2t FLOAT NULL,
    InitialMW FLOAT NULL,
    FinalMW FLOAT NULL,
    PQViewID INT NULL,
    CONSTRAINT UC_EventStat_EventID UNIQUE(EventID)
)
GO

CREATE VIEW OpenSEEScalarStatView AS
SELECT
    Event.ID AS EventID,
    MeterLocation.Name AS Station,
    Meter.Name AS Meter,
    Line.AssetKey AS LineKey,
    MeterLine.LineName,
    EventType.Name AS [Event Type],
    FORMAT(DATEDIFF(MILLISECOND, Event.StartTime, Event.EndTime) / 1000.0, '0.###') + ' seconds' AS [File Duration (s)],
    FORMAT(DATEDIFF(MILLISECOND, Event.StartTime, Event.EndTime) / System.Frequency, '0.##') + ' cycles' AS [File Duration (c)],
    FORMAT(FaultSummary.Distance, '0.##') + ' mi' AS [Fault Distance],
    FORMAT(FaultSummary.DurationSeconds * 1000.0, '0') + ' ms' AS [Fault Duration (ms)],
    FORMAT(FaultSummary.DurationCycles, '0.##') + ' cycles' AS [Fault Duration (c)],
    FORMAT(Sag.MagnitudePercent, '0.0') + '%' AS [Sag Magnitude (%)],
    FORMAT(Sag.MagnitudeVolts, '0') + ' volts' AS [Sag Magnitude (V)],
    FaultSummary.Algorithm,
    FORMAT(EventStat.VPeak, '0') + ' volts' AS [Voltage Peak],
    FORMAT(EventStat.VAMax, '0') + ' volts' AS [VA Maximum],
    FORMAT(EventStat.VBMax, '0') + ' volts' AS [VB Maximum],
    FORMAT(EventStat.VCMax, '0') + ' volts' AS [VC Maximum],
    FORMAT(EventStat.VABMax, '0') + ' volts' AS [VAB Maximum],
    FORMAT(EventStat.VBCMax, '0') + ' volts' AS [VBC Maximum],
    FORMAT(EventStat.VCAMax, '0') + ' volts' AS [VCA Maximum],
    FORMAT(EventStat.VAMin, '0') + ' volts' AS [VA Minimum],
    FORMAT(EventStat.VBMin, '0') + ' volts' AS [VB Minimum],
    FORMAT(EventStat.VCMin, '0') + ' volts' AS [VC Minimum],
    FORMAT(EventStat.VABMin, '0') + ' volts' AS [VAB Minimum],
    FORMAT(EventStat.VBCMin, '0') + ' volts' AS [VBC Minimum],
    FORMAT(EventStat.VCAMin, '0') + ' volts' AS [VCA Minimum],
    FORMAT(EventStat.IPeak, '0') + ' Amps' AS [Current Peak],
    FORMAT(EventStat.IAMax, '0') + ' Amps' AS [IA Maximum],
    FORMAT(EventStat.IBMax, '0') + ' Amps' AS [IB Maximum],
    FORMAT(EventStat.ICMax, '0') + ' Amps' AS [IC Maximum],
    FORMAT(EventStat.IA2t, '0') + ' (A^2)s' AS [IA2t],
    FORMAT(EventStat.IB2t, '0') + ' (A^2)s' AS [IB2t],
    FORMAT(EventStat.IC2t, '0') + ' (A^2)s' AS [IC2t],
    VAN.Mapping AS VAN,
    VBN.Mapping AS VBN,
    VCN.Mapping AS VCN,
    IAN.Mapping AS IAN,
    IBN.Mapping AS IBN,
    ICN.Mapping AS ICN,
    IR.Mapping AS IR
FROM
    Event JOIN
    MeterLine ON
        Event.MeterID = MeterLine.MeterID AND
        Event.LineID = MeterLine.LineID JOIN
    Meter ON Event.MeterID = Meter.ID JOIN
    MeterLocation ON Meter.MeterLocationID = MeterLocation.ID JOIN
    Line ON Event.LineID = Line.ID JOIN
    EventType ON Event.EventTypeID = EventType.ID LEFT OUTER JOIN
    FaultSummary ON
        Event.ID = FaultSummary.EventID AND
        FaultSummary.IsSelectedAlgorithm <> 0 AND
        FaultSummary.FaultNumber = 1 LEFT OUTER JOIN
    EventStat ON Event.ID = EventStat.EventID LEFT OUTER JOIN
    ChannelDetail VAN ON
        Event.MeterID = VAN.MeterID AND
        Event.LineID = VAN.LineID AND
        VAN.MeasurementType = 'Voltage' AND
        VAN.Phase = 'AN' AND
        VAN.MeasurementCharacteristic = 'Instantaneous' AND
        VAN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN
    ChannelDetail VBN ON
        Event.MeterID = VBN.MeterID AND
        Event.LineID = VBN.LineID AND
        VBN.MeasurementType = 'Voltage' AND
        VBN.Phase = 'BN' AND
        VBN.MeasurementCharacteristic = 'Instantaneous' AND
        VBN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN
    ChannelDetail VCN ON
        Event.MeterID = VCN.MeterID AND
        Event.LineID = VCN.LineID AND
        VCN.MeasurementType = 'Voltage' AND
        VCN.Phase = 'CN' AND
        VCN.MeasurementCharacteristic = 'Instantaneous' AND
        VCN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN
    ChannelDetail IAN ON
        Event.MeterID = IAN.MeterID AND
        Event.LineID = IAN.LineID AND
        IAN.MeasurementType = 'Current' AND
        IAN.Phase = 'AN' AND
        IAN.MeasurementCharacteristic = 'Instantaneous' AND
        IAN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN
    ChannelDetail IBN ON
        Event.MeterID = IBN.MeterID AND
        Event.LineID = IBN.LineID AND
        IBN.MeasurementType = 'Current' AND
        IBN.Phase = 'BN' AND
        IBN.MeasurementCharacteristic = 'Instantaneous' AND
        IBN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN
    ChannelDetail ICN ON
        Event.MeterID = ICN.MeterID AND
        Event.LineID = ICN.LineID AND
        ICN.MeasurementType = 'Current' AND
        ICN.Phase = 'CN' AND
        ICN.MeasurementCharacteristic = 'Instantaneous' AND
        ICN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN
    ChannelDetail IR ON
        Event.MeterID = IR.MeterID AND
        Event.LineID = IR.LineID AND
        IR.MeasurementType = 'Current' AND
        IR.Phase = 'RES' AND
        IR.MeasurementCharacteristic = 'Instantaneous' AND
        IR.SeriesType IN ('Values', 'Instantaneous') CROSS JOIN
    (
        SELECT COALESCE(CONVERT(FLOAT,
        (
            SELECT TOP 1 Value
            FROM Setting
            WHERE Name = 'SystemFrequency'
        )), 60.0) AS Frequency
    ) System OUTER APPLY
    (
        SELECT TOP 1
            Disturbance.PerUnitMagnitude * 100 AS MagnitudePercent,
            Disturbance.Magnitude AS MagnitudeVolts
        FROM
            Disturbance JOIN
            EventType ON
                Disturbance.EventTypeID = EventType.ID AND
                EventType.Name = 'Sag' JOIN
            Phase ON
                Disturbance.PhaseID = Phase.ID AND
                Phase.Name = 'Worst'
        WHERE
            Disturbance.EventID = Event.ID AND
            Disturbance.StartTime <= dbo.AdjustDateTime2(FaultSummary.Inception, FaultSummary.DurationSeconds) AND
            Disturbance.EndTime >= FaultSummary.Inception
    ) Sag
GO

ALTER PROCEDURE [dbo].[selectSitesDisturbancesDetailsByDate]
    -- Add the parameters for the stored procedure here
    @EventDate as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @startDate DateTime = @EventDate
    DECLARE @endDate DateTime

    if @context = 'day'
        SET @endDate = DATEADD(DAY, 1, @startDate);
    if @context = 'hour'
        SET @endDate = DATEADD(HOUR, 1, @startDate);
    if @context = 'minute'
        SET @endDate = DATEADD(MINUTE, 1, @startDate);
    if @context = 'second'
        SET @endDate = DATEADD(SECOND, 1, @startDate);

    DECLARE @PivotColumns NVARCHAR(MAX) = N''
    DECLARE @ReturnColumns NVARCHAR(MAX) = N''
    DECLARE @SQLStatement NVARCHAR(MAX) = N''

    create table #TEMP (Name varchar(max))
    insert into #TEMP SELECT SeverityCode FROM (Select Distinct SeverityCode FROM DisturbanceSeverity) as t

    SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(Name as varchar(5)), '') + '],'
    FROM #TEMP ORDER BY Name desc

    SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(Name as varchar(5)), '') + '], 0) AS [' + COALESCE(CAST(Name as varchar(5)), '') + '],'
    FROM #TEMP ORDER BY Name desc
    DECLARE @voltageEnvelope varchar(max) = (SELECT TOP 1 Value FROM Setting WHERE Name = 'DefaultVoltageEnvelope')

    SET @SQLStatement =
    'SELECT *
    INTO #authMeters
    FROM authMeters(@username)

    SELECT *
    INTO #meterSelections
    FROM String_To_Int_Table(@MeterID, '','')

    SELECT (SELECT TOP 1 ID FROM Event WHERE MeterID = pvt.MeterID AND StartTime >= @startDate AND StartTime < @endDate)
             EventID,
             MeterID,
             Site,
             ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + '
     FROM (
        SELECT
            Event.MeterID, COUNT(*) AS EventCount, SeverityCode, Meter.Name as Site
        FROM
            Disturbance JOIN
            DisturbanceSeverity ON Disturbance.ID = DisturbanceSeverity.DisturbanceID JOIN
            Event ON Disturbance.EventID = Event.ID JOIN
            EventType ON Event.EventTypeID = EventType.ID JOIN
            Meter ON Event.MeterID = Meter.ID JOIN
            Phase ON Phase.ID = Disturbance.PhaseID JOIN
            VoltageEnvelope ON VoltageEnvelope.ID = DisturbanceSeverity.VoltageEnvelopeID
        WHERE
            Phase.Name = ''Worst'' AND
            MeterID in (select * from #authMeters) AND MeterID IN (SELECT * FROM #meterSelections) AND
            VoltageEnvelope.Name = COALESCE(@voltageEnvelope, ''ITIC'') AND
            Event.StartTime >= @startDate AND Event.StartTime < @endDate
        GROUP BY Event.MeterID,Meter.Name,SeverityCode
           ) as ed
     PIVOT(
            SUM(ed.EventCount)
            FOR ed.SeverityCode IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ')
     ) as pvt
     ORDER BY MeterID '

    print @startDate
    print @endDate
    print @sqlstatement
    exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @voltageEnvelope VARCHAR(MAX) ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate, @voltageEnvelope = @voltageEnvelope
END

GO

ALTER PROCEDURE [dbo].[selectSitesEventsDetailsByDate]
    -- Add the parameters for the stored procedure here
    @EventDate as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @startDate DateTime = @EventDate
    DECLARE @endDate DateTime

    if @context = 'day'
        SET @endDate = DATEADD(DAY, 1, @startDate);
    if @context = 'hour'
        SET @endDate = DATEADD(HOUR, 1, @startDate);
    if @context = 'minute'
        SET @endDate = DATEADD(MINUTE, 1, @startDate);
    if @context = 'second'
        SET @endDate = DATEADD(SECOND, 1, @startDate);

    DECLARE @PivotColumns NVARCHAR(MAX) = N''
    DECLARE @ReturnColumns NVARCHAR(MAX) = N''
    DECLARE @SQLStatement NVARCHAR(MAX) = N''

    SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(t.Name as varchar(max)), '') + '],'
    FROM (Select Name FROM EventType) AS t

    SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(t.Name as varchar(max)), '') + '], 0) AS [' + COALESCE(CAST(t.Name as varchar(max)), '') + '],'
    FROM (Select Name FROM EventType) AS t

    SET @SQLStatement =
    'SELECT *
    INTO #authMeters
    FROM authMeters(@username)

    SELECT *
    INTO #meterSelections
    FROM String_To_Int_Table(@MeterID, '','')

    SELECT (SELECT TOP 1 ID FROM Event WHERE MeterID = pvt.MeterID AND StartTime >= @startDate AND StartTime < @endDate)
             EventID,
             MeterID,
             Site,
             ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + '
     FROM (
        SELECT
            Event.MeterID, COUNT(*) AS EventCount, EventType.Name, Meter.Name as Site
            FROM Event JOIN
            EventType ON Event.EventTypeID = EventType.ID JOIN
            Meter ON Event.MeterID = Meter.ID
           WHERE
            MeterID in (select * from #authMeters) AND MeterID IN (SELECT * FROM #meterSelections) AND
            StartTime >= @startDate AND StartTime < @endDate
           GROUP BY Event.MeterID,Meter.Name,EventType.Name
           ) as ed
     PIVOT(
            SUM(ed.EventCount)
            FOR ed.Name IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ')
     ) as pvt
     ORDER BY MeterID '

    print @startDate
    print @endDate
    print @sqlstatement
    exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate
END
GO

ALTER PROCEDURE [dbo].[selectSitesFaultsDetailsByDate]
    -- Add the parameters for the stored procedure here
    @EventDate as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @startDate DateTime = @EventDate
    DECLARE @endDate DateTime

    if @context = 'day'
        SET @endDate = DATEADD(DAY, 1, @startDate);
    if @context = 'hour'
        SET @endDate = DATEADD(HOUR, 1, @startDate);
    if @context = 'minute'
        SET @endDate = DATEADD(MINUTE, 1, @startDate);
    if @context = 'second'
        SET @endDate = DATEADD(SECOND, 1, @startDate);

    SELECT *
    INTO #authMeters
    FROM authMeters(@username)

    SELECT *
    INTO #meterSelections
    FROM String_to_int_table(@MeterID, ',')

    ; WITH FaultDetail AS
    (
        SELECT
            FaultSummary.ID AS thefaultid,
            Meter.Name AS thesite,
            Meter.ShortName AS theshortsite,
            MeterLocation.ShortName AS locationname,
            Meter.ID AS themeterid,
            Line.ID AS thelineid,
            Event.ID AS theeventid,
            MeterLine.LineName AS thelinename,
            Line.VoltageKV AS voltage,
            CAST(CAST(Event.StartTime AS TIME) AS NVARCHAR(100)) AS theinceptiontime,
            FaultSummary.FaultType AS thefaulttype,
            CASE WHEN FaultSummary.Distance = '-1E308' THEN 'NaN' ELSE CAST(CAST(FaultSummary.Distance AS DECIMAL(16,2)) AS NVARCHAR(19)) END AS thecurrentdistance,
            (SELECT COUNT(*) FROM FaultNote WHERE FaultSummary.ID = FaultNote.FaultSummaryID) as notecount,
            ROW_NUMBER() OVER(PARTITION BY Event.ID ORDER BY FaultSummary.IsSuppressed, FaultSummary.IsSelectedAlgorithm DESC, FaultSummary.Inception) AS rk
        FROM
            FaultSummary JOIN
            Event ON FaultSummary.EventID = Event.ID JOIN
            EventType ON Event.EventTypeID = EventType.ID JOIN
            Meter ON Event.MeterID = Meter.ID JOIN
            MeterLocation ON Meter.MeterLocationID = MeterLocation.ID JOIN
            Line ON Event.LineID = Line.ID JOIN
            MeterLine ON MeterLine.MeterID = Meter.ID AND MeterLine.LineID = Line.ID
        WHERE
            EventType.Name = 'Fault' AND
            Event.StartTime >= @startDate AND Event.StartTime < @endDate AND
            Meter.ID IN (SELECT * FROM #meterSelections) AND
            Meter.ID IN (select * from #authMeters)
    )
    SELECT *
    FROM FaultDetail
    WHERE rk = 1
END
GO

ALTER PROCEDURE [dbo].[selectSitesExtensionsDetailsByDate]
    -- Add the parameters for the stored procedure here
    @EventDate as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @startDate DateTime = @EventDate
    DECLARE @endDate DateTime

    if @context = 'day'
        SET @endDate = DATEADD(DAY, 1, @startDate);
    if @context = 'hour'
        SET @endDate = DATEADD(HOUR, 1, @startDate);
    if @context = 'minute'
        SET @endDate = DATEADD(MINUTE, 1, @startDate);
    if @context = 'second'
        SET @endDate = DATEADD(SECOND, 1, @startDate);

    DECLARE @PivotColumns NVARCHAR(MAX) = N''
    DECLARE @ReturnColumns NVARCHAR(MAX) = N''
    DECLARE @SQLStatement NVARCHAR(MAX) = N''
    DECLARE @MiddleStatment NVARCHAR(MAX) = N''

    SELECT @PivotColumns = @PivotColumns + '[' + t.ServiceName + '],'
    FROM (Select ServiceName FROM EASExtension) AS t

    SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + t.ServiceName + '], 0) AS [' + t.ServiceName + '],'
    FROM (Select ServiceName FROM EASExtension) AS t

    SELECT @MiddleStatment = @MiddleStatment +  '
            SELECT MeterID,
                '''+ t.ServiceName + ''' as ServiceName,
                Count(*) as EventCount
            FROM    #temp
            WHERE   dbo.' + t.HasResultFunction + '(ID) != ''''
            GROUP BY MeterID UNION'

    FROM (Select * FROM EASExtension) AS t

    SET @SQLStatement =
    'SELECT *
    INTO #authMeters
    FROM authMeters(@username)

    SELECT *
    INTO #meterSelections
    FROM String_To_Int_Table(@MeterID, '','')

    SELECT * INTO #temp
    FROM EVENT
    WHERE   StartTime Between @startDate AND @endDate AND
            MeterID in (select * from authMeters(@username)) AND
            MeterID IN (SELECT * FROM String_To_Int_Table( @MeterID,  '',''))

    SELECT (SELECT TOP 1 ID FROM Event WHERE MeterID = Meter.ID AND StartTime >= @startDate AND StartTime < @endDate) as EventID,
             Meter.ID as MeterID,
             Meter.Name as Site,
             ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + '
     FROM Meter Join
        (
            ' + SUBSTRING(@MiddleStatment,0, LEN(@MiddleStatment) - LEN('UNION')) + '
         ) as ed
         PIVOT(
                SUM(ed.EventCount)
                FOR ed.ServiceName IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ')
         ) as pvt ON pvt.MeterID = Meter.ID
     WHERE
        Meter.ID in (select * from #authMeters) AND Meter.ID IN (SELECT * FROM #meterSelections)

     ORDER BY MeterID
     DROP Table #temp
     '

    --print @startDate
    --print @endDate
    print @sqlstatement
    exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate

END
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('FaultLocation.FaultCalculationCycleMethod', 'MaxCurrent', 'MaxCurrent')
GO